***Table 6 - Firm returns 
cd "S:\Katja_Aksel\JFQA index paper\v3\pseudo_data"
clear
set more 1
cap log close

log using ./logs/Table6.log, replace

use ./4_postmoney_valuations.dta, clear
tsset orgnr time
keep if mv_rounds!=.
*64,377 rounds
sort orgnr time
bys orgnr: gen total_rounds=_N
keep orgnr total_rounds
duplicates drop
sum total_rounds, d
/*
                        total_rounds
-------------------------------------------------------------
      Percentiles      Smallest
 1%            1              1
 5%            1              1
10%            1              1       Obs              45,725
25%            1              1       Sum of wgt.      45,725

50%            1                      Mean           1.407917
                        Largest       Std. dev.      1.157861
75%            1             22
90%            2             23       Variance       1.340642
95%            3             26       Skewness       5.839319
99%            7             27       Kurtosis       57.97707


45,725 firms in total (HIP+non-HIP) with at least one financing round
*/
merge m:1 orgnr using ./7_population_firmTVPI.dta
replace total_rounds=0 if total_rounds==.
drop _merge
sum TVPI*
/*
    Variable |        Obs        Mean    Std. dev.       Min        Max
-------------+---------------------------------------------------------
       TVPI1 |     45,706    2.316159    214.6428   .0025073    45886.4
       TVPI2 |     71,559    1.213036     171.542          0    45886.4
       TVPI3 |     78,111    63.27719    15702.58          0    4387500
*/

****equally weighted-distributions
**all VC-backed firms
outreg2 using ./tables/Table6ew.xls if sample==1 & vcbacked==1, replace sum(detail) keep(TVPI*) eqkeep(N mean sd skewness p10 p50 p75 p90 p95 p99)
**all HIP firms
outreg2 using ./tables/Table6ew.xls if sample==1, append sum(detail) keep(TVPI*) eqkeep(N mean sd skewness p10 p50 p75 p90 p95 p99)
***HIP+VC-backed multi-round firms
outreg2 using ./tables/Table6ew.xls if sample==1 & vcbacked==1 & total_rounds>1, append sum(detail) keep(TVPI*) eqkeep(N mean sd skewness p10 p50 p75 p90 p95 p99)
***HIP+non-VC-backed multi-round firms
outreg2 using ./tables/Table6ew.xls if sample==1 & vcbacked==0 & total_rounds>1, append sum(detail) keep(TVPI*) eqkeep(N mean sd skewness p10 p50 p75 p90 p95 p99)

****amount weighted-distributions
**all VC-backed firms
outreg2 using ./tables/Table6cw.xls if sample==1 & vcbacked==1 [aweight=cumequity3], replace sum(detail) keep(TVPI*) eqkeep(N mean sd skewness p10 p50 p75 p90 p95 p99)
**all HIP firms
outreg2 using ./tables/Table6cw.xls if sample==1 [aweight=cumequity3], append sum(detail) keep(TVPI*) eqkeep(N mean sd skewness p10 p50 p75 p90 p95 p99)
***HIP+VC-backed multi-round firms
outreg2 using ./tables/Table6cw.xls if sample==1 & vcbacked==1 & total_rounds>1 [aweight=cumequity3], append sum(detail) keep(TVPI*) eqkeep(N mean sd skewness p10 p50 p75 p90 p95 p99)
***HIP+non-VC-backed multi-round firms
outreg2 using ./tables/Table6cw.xls if sample==1 & vcbacked==0 & total_rounds>1 [aweight=cumequity3], append sum(detail) keep(TVPI*) eqkeep(N mean sd skewness p10 p50 p75 p90 p95 p99)

***now show winsorized means
foreach t in TVPI1 TVPI2 TVPI3 {
sum `t', d
return list
sum `t' if `t'>r(p99), d
return list
replace `t'=r(p50) if `t'>r(p50) & `t'!=.
}

****equally weighted-distributions
**all VC-backed firms
outreg2 using ./tables/Table6ew.xls if sample==1 & vcbacked==1, append sum(detail) keep(TVPI*) eqkeep(N mean p99)
**all HIP firms
outreg2 using ./tables/Table6ew.xls if sample==1, append sum(detail) keep(TVPI*) eqkeep(N mean p99)
***HIP+VC-backed multi-round firms
outreg2 using ./tables/Table6ew.xls if sample==1 & vcbacked==1 & total_rounds>1, append sum(detail) keep(TVPI*) eqkeep(N mean p99)
***HIP+non-VC-backed multi-round firms
outreg2 using ./tables/Table6ew.xls if sample==1 & vcbacked==0 & total_rounds>1, append sum(detail) keep(TVPI*) eqkeep(N mean p99)

****amount weighted-distributions
**all VC-backed firms
outreg2 using ./tables/Table6cw.xls if sample==1 & vcbacked==1 [aweight=cumequity3], append sum(detail) keep(TVPI*) eqkeep(N mean p99)
**all HIP firms
outreg2 using ./tables/Table6cw.xls if sample==1 [aweight=cumequity3], append sum(detail) keep(TVPI*) eqkeep(N mean p99)
***HIP+VC-backed multi-round firms
outreg2 using ./tables/Table6cw.xls if sample==1 & vcbacked==1 & total_rounds>1 [aweight=cumequity3], append sum(detail) keep(TVPI*) eqkeep(N mean p99)
***HIP+non-VC-backed multi-round firms
outreg2 using ./tables/Table6cw.xls if sample==1 & vcbacked==0 & total_rounds>1 [aweight=cumequity3], append sum(detail) keep(TVPI*) eqkeep(N mean p99)



